# _*_ coding:utf-8 _*_
# @Time  : 2023.03.29
# @Author: zizlee
import json
import pathlib
import pandas as pd
import datetime
from database import LocalDataBase

pd.set_option('display.max_columns', None) # 展示所有列
pd.set_option('display.width', 1000) # 展示所有列
pd.set_option('display.unicode.ambiguous_as_wide', True)
pd.set_option('display.unicode.east_asian_width', True)


# 成交明细表
class ExchangeDetailHandler:
    def __init__(self, handle_date: datetime.date, is_date: bool = False):
        self.handle_date = handle_date
        self.handle_date_str = handle_date.strftime('%Y%m%d')
        self.local_file = pathlib.Path(f'src/ExchangeDetail/cjmx_{self.handle_date_str}.csv')
        if is_date:
            self.local_file = pathlib.Path(f'src/{self.handle_date_str}/cjmx_{self.handle_date_str}.csv')

    def save(self, is_test=False):
        if not self.local_file.exists():
            print('{}的成交明细表数据不存在!'.format(self.handle_date_str))
            return
        df = pd.read_csv(self.local_file, dtype={0: str, 1: str})
        df['cust_name'].fillna('', inplace=True)
        df['contract'] = df['contract'].apply(lambda x: x.upper())
        # wt_kind委托类型有空数据
        df['wt_kind'].fillna('', inplace=True)
        if (df.isnull().any()).any():
            print('存在缺失数据,如下列中:')
            print(df.isnull().any())
            raise ValueError('含有缺失数据nan')
        if is_test:
            return
        sql = """
            INSERT INTO exchange_detail (ex_date,cust_num,cust_name,seat_name,protect,hands,ex_price,
              ex_amount,charge,wt_kind,exl_name,variety_name,delivery,direction,oc_kind,ex_num,
              ex_kind,contract,close_flag) 
            VALUES (%(ex_date)s,%(cust_num)s,%(cust_name)s,%(seat_name)s,%(protect)s,%(hands)s,%(ex_price)s,
              %(ex_amount)s,%(charge)s,%(wt_kind)s,%(exl_name)s,%(variety_name)s,%(delivery)s,%(direction)s,
              %(oc_kind)s,%(ex_num)s,%(ex_kind)s,%(contract)s,%(close_flag)s);
        """
        prm = df.to_dict(orient='records')
        print('准备保存{}成交明细表，数量：{}'.format(self.handle_date_str, len(prm)))
        with LocalDataBase() as cursor:
            cursor.execute("SELECT id FROM exchange_detail WHERE ex_date=%s LIMIT 1;", (self.handle_date_str, ))
            is_exist = cursor.fetchone()
            if is_exist:
                print('{}成交明细数据已存在，无需重复保存!'.format(self.handle_date_str))
                return
            c = cursor.executemany(sql, prm)
        print('本地保存{}成交明细表数据成功，数量:{}'.format(self.handle_date_str, c))


# 平仓明细表
class CloseDetailHandler:
    def __init__(self, handle_date: datetime.date, is_date: bool = False):
        self.handle_date = handle_date
        self.handle_date_str = handle_date.strftime('%Y%m%d')
        self.local_file = pathlib.Path(f'src/CloseDetail/pcmx_{self.handle_date_str}.csv')
        if is_date:
            self.local_file = pathlib.Path(f'src/{self.handle_date_str}/pcmx_{self.handle_date_str}.csv')

    def save(self, is_test=False):
        if not self.local_file.exists():
            print('{}的平仓明细表数据不存在!'.format(self.handle_date_str))
            return
        df = pd.read_csv(self.local_file, dtype={0: str, 1: str})
        df['cust_name'].fillna('', inplace=True)
        df['contract'] = df['contract'].apply(lambda x: x.upper())  # 合约转大写
        df.sort_values(by='cust_num', inplace=True)
        if (df.isnull().any()).any():
            print('存在缺失数据,如下列中:')
            print(df.isnull().any())
            raise ValueError('含有缺失数据nan')
        if is_test:
            return
        sql = """
            INSERT INTO close_detail (ex_date,cust_num,cust_name,clp_num,exl_name,seat_name,ex_kind,variety_name,
              delivery,contract,direction,hands,price_open,price_close,
              profit_drift,profit_close,date_open,dto_num,
              ex_num,cl_kind,protect) 
            VALUES (%(ex_date)s,%(cust_num)s,%(cust_name)s,%(clp_num)s,%(exl_name)s,
              %(seat_name)s,%(ex_kind)s,%(variety_name)s,%(delivery)s,%(contract)s,%(direction)s,%(hands)s,
              %(price_open)s,%(price_close)s,%(profit_drift)s,%(profit_close)s,%(date_open)s,
              %(dto_num)s,%(ex_num)s,%(cl_kind)s,%(protect)s);
        """
        prm = df.to_dict(orient='records')
        print('准备保存{}平仓明细表，数量：{}'.format(self.handle_date_str, len(prm)))
        with LocalDataBase() as cursor:
            cursor.execute("SELECT id FROM close_detail WHERE ex_date=%s LIMIT 1;", (self.handle_date_str,))
            is_exist = cursor.fetchone()
            if is_exist:
                print('{}平仓明细数据已存在，无需重复保存!'.format(self.handle_date_str))
                return
            c = cursor.executemany(sql, prm)
        print('本地保存{}平仓明细表成功，数量:{}'.format(self.handle_date_str, c))


# 历史成交单
class HistoryExchangeHandler:
    def __init__(self, handle_date: datetime.date, is_date: bool = False):
        self.handle_date = handle_date
        self.handle_date_str = handle_date.strftime('%Y%m%d')
        self.local_file = pathlib.Path(f'src/HistoryExchange/cjdgl_{self.handle_date_str}.csv')
        if is_date:
            self.local_file = pathlib.Path(f'src/{self.handle_date_str}/cjdgl_{self.handle_date_str}.csv')

    def save(self, is_test=False):
        if not self.local_file.exists():
            print('{}历史成交单不存在.'.format(self.local_file))
            return
        df = pd.read_csv(self.local_file, dtype={0: str, 2: str})
        df['cust_name'].fillna('', inplace=True)
        df['contract'] = df['contract'].apply(lambda x: x.upper())
        df.sort_values(by='cust_num')
        if (df.isnull().any()).any():
            print('存在缺失数据,如下列中:')
            print(df.isnull().any())
            raise ValueError('含有缺失数据nan')
        if is_test:
            return
        sql = """
            INSERT INTO exchange_history 
              (ex_date,ex_time,cust_num,cust_name,exl_name,ex_kind,variety_name,contract,direction,
              oc_kind,hands,ex_price,ex_amount,charge,ex_charge) 
            VALUES (%(ex_date)s,%(ex_time)s,%(cust_num)s,%(cust_name)s,%(exl_name)s,%(ex_kind)s,%(variety_name)s,
              %(contract)s,%(direction)s,%(oc_kind)s,%(hands)s,%(ex_price)s,%(ex_amount)s,%(charge)s,%(ex_charge)s);
        """
        prm = df.to_dict(orient='records')
        print('准备保存{}成交单管理表，数量：{}'.format(self.handle_date_str, len(prm)))
        with LocalDataBase() as cursor:
            cursor.execute("SELECT id FROM exchange_history WHERE ex_date=%s LIMIT 1;", (self.handle_date_str,))
            is_exist = cursor.fetchone()
            if is_exist:
                print('{}成交单管理数据已存在，无需重复保存!'.format(self.handle_date_str))
                return
            c = cursor.executemany(sql, prm)
        print('本地保存{}成交单管理表成功，数量：{}'.format(self.handle_date_str, c))


# 交易统计表
class ExchangeTotalHandler:
    def __init__(self, handle_date: datetime.date, is_date: bool = False):
        self.handle_date = handle_date
        self.handle_date_str = handle_date.strftime('%Y%m%d')
        self.local_file = pathlib.Path(f'src/ExchangeTotal/jytj_{self.handle_date_str}.csv')
        if is_date:
            self.local_file = pathlib.Path(f'src/{self.handle_date_str}/jytj_{self.handle_date_str}.csv')

    def save(self, is_test=False):
        if not self.local_file.exists():
            print('{}交易统计表不存在.'.format(self.local_file))
            return
        df = pd.read_csv(self.local_file, dtype={0: str, 1: str})
        df['cust_name'].fillna('', inplace=True)
        if (df.isnull().any()).any():
            print('存在缺失数据,如下列中:')
            print(df.isnull().any())
            print(df[df['variety_name'].isnull()])
            raise ValueError('含有缺失数据nan')
        if is_test:
            return
        sql = """
            INSERT INTO exchange_total 
              (ex_date,cust_num,cust_name,exl_name,variety_name,hands,ex_amount,hands_close,amount_close,hands_holdings,
              profit_drift,profit,profit_net,fund_bail,fund_bail_exl,charge,charge_exl,charge_odd,charge_net) 
            VALUES (%(ex_date)s,%(cust_num)s,%(cust_name)s,%(exl_name)s,%(variety_name)s,%(hands)s,%(ex_amount)s,
              %(hands_close)s,%(amount_close)s,%(hands_holdings)s,%(profit_drift)s,%(profit)s,%(profit_net)s,
              %(fund_bail)s,%(fund_bail_exl)s,%(charge)s,%(charge_exl)s,%(charge_odd)s,%(charge_net)s);
        """
        prm = df.to_dict(orient='records')
        print('准备保存{}交易统计表，数量：{}'.format(self.handle_date_str, len(prm)))
        with LocalDataBase() as cursor:
            cursor.execute("SELECT id FROM exchange_total WHERE ex_date=%s LIMIT 1;", (self.handle_date_str,))
            is_exist = cursor.fetchone()
            if is_exist:
                print('{}交易统计数据已存在，无需重复保存!'.format(self.handle_date_str))
                return
            c = cursor.executemany(sql, prm)
        print('本地保存{}交易统计表数据成功，数量：{}'.format(self.handle_date_str, c))


# 资金对帐表
class FundBalanceHandler:
    def __init__(self, handle_date: datetime.date, is_date: bool = False):
        self.handle_date = handle_date
        self.handle_date_str = handle_date.strftime('%Y%m%d')
        self.local_file = pathlib.Path(f'src/FundBalance/zjdz_{self.handle_date_str}.csv')
        if is_date:
            self.local_file = pathlib.Path(f'src/{self.handle_date_str}/zjdz_{self.handle_date_str}.csv')

    def save(self, is_test=False):
        if not self.local_file.exists():
            print('{}资金对账表数据不存在!'.format(self.handle_date_str))
            return
        df = pd.read_csv(self.local_file, dtype={0: str, 1: str})
        df['cust_name'].fillna('', inplace=True)
        df.sort_values(by='cust_num', inplace=True)
        if (df.isnull().any()).any():
            print('存在缺失数据,如下列中:')
            print(df.isnull().any())
            raise ValueError('含有缺失数据nan')
        if is_test:
            return
        sql = """
            INSERT INTO fund_balance (ex_date,cust_num,cust_name,sett_previous,fund_out,fund_in,
              equity_end,fund_avail,sett_current,fund_bail,fund_bail_exl,charge,charge_exl,
              charge_odd,charge_net,profit,profit_close,profit_drift,profit_fd) 
            VALUES (%(ex_date)s,%(cust_num)s,%(cust_name)s,%(sett_previous)s,%(fund_out)s,
              %(fund_in)s,%(equity_end)s,%(fund_avail)s,%(sett_current)s,%(fund_bail)s,%(fund_bail_exl)s,
              %(charge)s,%(charge_exl)s,%(charge_odd)s,%(charge_net)s,%(profit)s,%(profit_close)s,
              %(profit_drift)s,%(profit_fd)s);
        """
        prm = df.to_dict(orient='records')
        print('准备保存{}资金对账表，数量：{}'.format(self.handle_date_str, len(prm)))
        with LocalDataBase() as cursor:
            cursor.execute("SELECT id FROM fund_balance WHERE ex_date=%s LIMIT 1;", (self.handle_date_str,))
            is_exist = cursor.fetchone()
            if is_exist:
                print('{}资金对账表数据已存在，无需重复保存!'.format(self.handle_date_str))
                return
            c = cursor.executemany(sql, prm)
        print('本地保存{}资金对账表数据成功，数量：{}'.format(self.handle_date_str, c))


if __name__ == '__main__':
    with open('ex_dates.json', 'r', encoding='utf8') as dtf:
        date_dict = json.load(dtf)
    test = False
    only_date = True
    if only_date:
        date_list = ['20231215']
    else:
        date_list = date_dict['2023']
    for d in date_list:
        d_date = datetime.date(int(d[0:4]), int(d[4:6]), int(d[6:8]))
        if d_date <= datetime.date(2023, 4, 14):
            continue
        cjmx = ExchangeDetailHandler(handle_date=d_date, is_date=only_date)
        cjmx.save(is_test=test)

        pcmx = CloseDetailHandler(handle_date=d_date, is_date=only_date)
        pcmx.save(is_test=test)

        # cjdgl = HistoryExchangeHandler(handle_date=d_date, is_date=only_date)
        # cjdgl.save(is_test=test)

        jytj = ExchangeTotalHandler(handle_date=d_date, is_date=only_date)
        jytj.save(is_test=test)

        zjdz = FundBalanceHandler(handle_date=d_date, is_date=only_date)
        zjdz.save(is_test=test)

        if test:
            print(d_date)

